Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ARGUMENT

Within an OLAP DML program, the ARGUMENT statement declares an argument that is expected by the program. Within the program, the argument is stored in a structure similar to a variable or valueset. The argument is initialized with the value that was passed when the program was invoked. An argument exists only while the program is running.

The ARGUMENT statement is used only in programs, and it must precede the first executable line in the program. Be careful to distinguish the ARG abbreviation of the ARGUMENT statement from the ARG function.

Syntax

ARGUMENT name {datatype|dimension|VALUESET dim}

Arguments

name

  The name by which the argument will be referenced in the program. An argument cannot have the same name as a local variable or valueset. You name an argument according to the rules for naming analytic workspace objects (see the DEFINE command).

datatype

  The data type of the argument, which indicates the kind of data to be stored. You can specify any of the data types that are listed and described in the DEFINE VARIABLE entry. Also, when you want to the program to be able to receive an argument without converting it to a specific datatype, you can also specify `WORKSHEET` for the data type.

  Important: When you declare an argument to be of type NTEXT, and a TEXT value is passed into the program, Oracle OLAP converts the TEXT value to NTEXT. Similarly, when you declare an argument to be of type TEXT, and an NTEXT value is passed into the program, Oracle OLAP converts the NTEXT value to TEXT. Data can be lost when NTEXT is converted to TEXT.

dimension

  The name of a dimension, whose value will be contained in the argument. The argument will hold a single value of the dimension. Assigning a value that does not currently exist in the dimension causes an error.

VALUESET dim

  Indicates that name is a valueset. The keyword dim specifies the dimension for which the valueset holds values. Argument valuesets can be used within the program in the same way you would use a valueset in the analytic workspace.

Notes

The Life Span of an Argument

An argument exists only while the program in which it is declared is running. When the program terminates, the argument ceases to exist and its value is lost. Therefore, an argument is not an analytic workspace object.

A program can terminate when a RETURN or SIGNAL statement, or at the last line of the program executes. When the program calls a subprogram, the original program is temporarily suspended and the argument still exists when the subprogram ends and control returns to the original program. A program that calls itself recursively has separate arguments for each running copy of the program.

Declaring Arguments that Will be Passed Into a Program

When declaring arguments that are passed into a program special considerations apply.

Arguments Passed by Value Arguments are passed into a program by value. This means that the called program is given only the value of an argument, without access to any analytic workspace object to which it might be related. Therefore, you can change an argument value within the called program without affecting any value outside the program. You can think of an argument variable or valueset as a conveniently initialized local variable or local valueset.

Argument Processing for a Function When a program is invoked either with a CALL statement or as a function, the following two-step process occurs:

1. The specified data types are established. Argument expressions specified by the calling program are evaluated left to right, and their data types are identified. An expression representing a dimension value can be a text (`TEXT` or `ID`), numeric (`INTEGER`, `DECIMAL`, and so on), or RELATION value. An error in one argument expression stops the process.

2. Each specified data type is matched with the declared data type. Argument expressions are matched positionally with the declared arguments. The first argument expression is matched with the first declared argument, the second argument expression with the second argument, and so on. Each expression is converted in turn to the declared data type of the declared argument.

When an argument is declared as a dimension value, the matching value passed from the calling program can be `TEXT` or `ID` (representing a value of the specified dimension), numeric (representing a logical dimension position), or RELATION (representing a physical dimension position). The RELATION method is the way Oracle OLAP passes along dimension values that are the result of evaluating a dimension name or relation name used as the matching value. When the matching value is a noninteger numeric value (for example, `DECIMAL`), it is rounded to the nearest `INTEGER` value to represent a logical dimension position.

When an argument is declared as something other than a dimension value, and the matching value from the calling program is a RELATION value, an error will occur. When you want to pass a RELATION value and receive it as a `TEXT` argument, use CONVERT to convert the value in the program's argument list.

When an argument is declared as a valueset of a dimension, only the name of a valueset of that dimension will be accepted as an argument.

When an error occurs in either the first or second step, the program is not executed.

Argument Processing for a Command When a program is invoked as a standalone command with its arguments not enclosed by parentheses, the arguments are matched positionally with the declared arguments. The called program can reference the specified arguments either as declared arguments or through the ARG (n), ARGS, and ARGFR (n) functions. In this situation, the arguments are passed as text strings, not by value.

Extra Arguments When the calling program specifies more arguments than there are declarations in the called program, the extra arguments are ignored. When the calling program specifies fewer arguments than there are declarations in the called program, the extra arguments are given `NA` values.

Argument Name that Duplicate the Names of Analytic Workspace Objects

Ordinarily, when you give an argument the same name as an analytic workspace object, the argument (not the analytic workspace object) will be referenced within the program. Exceptions to this rule occur only when the statement in which the reference is made requires an analytic workspace object as an argument. The following OLAP DML statements require an analytic workspace object to be specified as an argument.

  CONSIDER
  COPYDFN
  DEFINE
  DELETE
  DESCRIBE
  EXPORT
  IMPORT
  LOAD
  MOVE
  POP
  PUSH
  RENAME
  STATUS

Examples

Example 8-7 Passing an Argument to a User-Defined Function

Sometimes verifying user input to the GET function can become complicated. The usual method involves a line of code such as the following one.

    SHOW GET(INT VERIFY VALUE GT 0 AND VALUE LT 100 -
       IFNOT 'The value must be between 1 and 100')

You can create a user-defined function to make the GET expression simpler. For example, the following program can be used as a function to check for values between `0` and `100`.

    DEFINE verit PROGRAM BOOLEAN
    PROGRAM
      ARGUMENT uservalue INT
      TRAP ON haderror NOPRINT
      IF uservalue GT 100
         THEN SIGNAL toobig 'The value must be 100 or smaller.'
      ELSE IF uservalue LT 0
         THEN SIGNAL toosmall 'The value must be 0 or greater.'
      RETURN TRUE
    haderror:
      RETURN FALSE
    END

The following GET expression uses the `verit` function.

    SHOW GET(INT VERIFY VERIT(VALUE) IFNOT ERRORTEXT)

Example 8-8 Passing Multiple Arguments

Suppose, in the `product.rpt` program, that you want to supply a second argument that specifies the column width for the data columns in the report. In the `product.rpt` program, you would add a second ARGUMENT statement to declare the `INTEGER` argument to be used in setting the value of the COLWIDTH option.

    ARGUMENT natext TEXT
    ARGUMENT widthamt INTEGER
    NASPELL = natext
    COLWIDTH = widthamt

To specify eight-character columns, you could run the `product.rpt` program with the following statement.

    CALL product.rpt ('Missing' 8)

When the `product.rpt` program also requires the name of a product as a third argument, then in the `product.rpt` program you would add a third ARGUMENT statement to handle the product argument, and you would set the status of the `product` dimension using this argument.

    ARGUMENT natext TEXT
    ARGUMENT widthamt INTEGER
    ARGUMENT rptprod PRODUCT
    NASPELL = natext
    COLWIDTH = widthamt
    LIMIT product TO rptprod

You can run the `product.rpt` program with the following statement.

    CALL product.rpt ('Missing' 8 'TENTS')

In this example, the third argument is specified in uppercase letters with the assumption that all the dimension values in the analytic workspace are in uppercase letters.

Example 8-9 Using the ARGUMENT Statement

Suppose you are writing a program, called `product.rpt`. The `product.rpt` program produces a report, and you want to supply an argument to the report program that specifies the text that should appear for an NA value in the report. In the `product.rpt` program, you can use the declared argument `natext` in an ARGUMENT statement to set the NASPELL option to the value provided as an argument.

    ARGUMENT natext TEXT
    NASPELL = natext

To specify `Missing` as the text for NA values, you can execute the following statement.

    CALL product.rpt ('Missing')

In this example, literal text enclosed in single quotes provides the value of the text argument. However, any other type of text expression works equally well, as shown in the next example.

    DEFINE natemp VARIABLE TEXT TEMP
    natemp = 'Missing'
    CALL product.rpt (natemp)

Example 8-10 Passing the Text of an Expression

Suppose you have a program named `custom.rpt` that includes a REPORT statement, but you want to be able to use the program to present the values of an expression, such as `sales - expense`, as well as single variables.

    custom.rpt 'sales - expense'

Note that you must enclose the expression in single quotation marks. Because the expression contains punctuation (the minus sign), the quotation marks are necessary to indicate that the entire expression is a single argument.

In the `custom.rpt` program, you could use the following statements to produce a report of this expression.

    ARGUMENT rptexp TEXT
    REPORT &rptexp

For an example of using ampersand substitution to pass multiple dimension values, see "Using Ampersand Substitution with LIMIT".

Example 8-11 Passing Workspace Object Names and Keywords

Suppose you design a program called `sales.rpt` that produces a report on a variable that is specified as an argument and sorts the `product` dimension in the order that is specified in another argument. You would run the `sales.rpt` program by executing a statement like the following one.

    sales.rpt units d

In the `sales.rpt` program, you can use the following statements.

    ARGUMENT varname TEXT
    ARGUMENT sortkey TEXT
    SORT product &sortkey &varname
    REPORT &varname

After substituting the arguments, these statements are executed in the `sales.rpt` program.

    SORT product D units
    REPORT units